/*
Create data that maps BPS geocodes to 2000 Census Block Groups and their characteristics,
to be used in Table 1
*/

clear all

* import BPS geocode to Census block group mapping created from T1_2000_intersect.qgz
insheet using "$raw_data_census/QGIS Join/geo_census_intersect_2000_novars.csv", clear comma

rename bkgpidfp00 FIPS
tostring FIPS, gen(FIPS_string) format("%12.0f")
drop FIPS
rename FIPS_string FIPS

save "$stata_data_location/geo_census_intersect_2000_novars", replace

* Merge on demographic data
merge m:1 FIPS using "$raw_data_census/Social Explorer/census_2000.dta", keep(1 3)

* Create demographic variables of interest
gen pop = T001_001
gen share_white = T014_002/T014_001
gen share_black = T014_003/T014_001
gen share_hispanic = T015_010/T015_001
gen med_inc_hh = T093_001
gen med_inc_fam = T098_001
gen med_gross_rent = T167_001

gen pov = (T180_002 + T181_002 + T182_002) /(T180_001 + T181_001 + T182_001)

keep geocode pop pov share_white share_black share_hispanic med_inc_hh med_inc_fam med_gross_rent

save "$stata_data_location/geo_census_intersect_2000", replace
